Install the package for the first time.
devtools::install_github("ccb-hms/phonto")
library(phonto)
library(DT)
library(nhanesA)
sqlHost <- "localhost"
sqlUserName <- "sa"
sqlPassword <- "yourStrong(!)Password"
sqlDefaultDb <- "NhanesLandingZone"
It can be search by certain variable.
# nameonly <- nhanesSearchVarName('BPXPULS')
# nameonly
# replicate function
nameonly1 <- searchTablesByVar('BPXPULS')
nameonly1
#> Questionnaire TableName
#> 1 BPX BloodPressure
#> 2 BPX_B BloodPressure
#> 3 BPX_C BloodPressure
#> 4 BPX_D BloodPressure
#> 5 BPX_E BloodPressure
#> 6 BPX_F BloodPressure
#> 7 BPX_G BloodPressure
#> 8 BPX_H BloodPressure
#> 9 BPX_I BloodPressure
#> 10 BPX_J BloodPressure
# details <- nhanesSearchVarName('BPXPULS', includerdc=TRUE, ystart=2001, ystop=2008, nchar=20,namesonly=F)
# datatable(details)
# But we can do something like this now:
searchTableByName("BPX",ystart=2001, ystop=2008)
#> Questionnaire TableName
#> 1 BPX_B BloodPressure
#> 2 BPX_C BloodPressure
#> 3 BPX_D BloodPressure
#> 4 BPX_E BloodPressure
Replicated the name only version and need meta data in database to replicate the details
cols = c("RIDAGEYR","RIAGENDR","BMXBMI","DMDEDUC2")
data = jointQuery(c('BodyMeasures','DemographicVariablesAndSampleWeights'),cols)
DT::datatable(data)
tablnames = searchTableByName('BPX[_]')
tablnames
#> Questionnaire TableName
#> 1 BPX_B BloodPressure
#> 2 BPX_C BloodPressure
#> 3 BPX_D BloodPressure
#> 4 BPX_E BloodPressure
#> 5 BPX_F BloodPressure
#> 6 BPX_G BloodPressure
#> 7 BPX_H BloodPressure
#> 8 BPX_I BloodPressure
#> 9 BPX_J BloodPressure
blood_df <- unionQuery(tablnames$TableName,cols =c("BPXDI1","BPXDI2","BPXSY1","BPXSY2"))
DT::datatable(blood_df)
We need to ensure all the variables can be found across the same table group; otherwise, the function will throw an error.
searchTablesByVar("URXDMA")
#> Questionnaire TableName
#> 1 L06PHY_C PhytoestrogensUrine
#> 2 PHPYPA PhthalatesPhytoestrogensAndPAHsUrinePHPYPAUrinaryPhthalates
#> 3 PHPYPA_B PhthalatesPhytoestrogensAndPAHsUrine
#> 4 PHYTO_D PhytoestrogensUrine
#> 5 PHYTO_E PhytoestrogensUrine
#> 6 PHYTO_F PhytoestrogensUrine
phy_urine = c("URXDAZ","URXDMA","URXEQU", "URXETD","URXETL","URXGNS")
# another new function that allow users query data by a group variables
phy_urine_df = queryByVars(phy_urine)
DT::datatable(phy_urine_df)
tb_detail <- nhanesSearchTableNames('BPX', includerdc=TRUE, nchar=42, details=TRUE)
datatable(tb_detail)
For example,
var_detail <- variableDescr("BPX_D")
datatable(var_detail)
tables <- c("BPX_D","BPX_E","BPX","BPX_C")
cols <- c("BPXDI1","BPXDI2","BPXSY1","BPXSY2")
# bpx_data <- queryData(tables,cols)
# datatable(bpx_data)
In the following matrix, 0 means the tables have no data in that year, and 1 means they have data in corresponding years.
# mtx <- check_data()
# DT::datatable(
# mtx
# ) |> DT::formatStyle(colnames(mtx),
# backgroundColor = styleInterval(c(0, 1), c('gray', 'red',"white")),
# fontWeight = 'bold')